package com.demo.database.jdbc;

import com.demo.json.StudentDTO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

@SuppressWarnings({"Duplicates", "unused", "SqlNoDataSourceInspection", "SqlDialectInspection"})
public class DBUtil {
    // H2数据库地址
    // 如果数据库不存在，会在用户目录创建一个数据库文件，缺省用户是sa，密码是空字符串
    private static final String URL = "jdbc:h2:file:c:/H2DB/testH2;MODE=MYSQL;DB_CLOSE_DELAY=-1";
    // 本地连接的内存模式，程序模式时，应用程序结束后，数据库就被清除。
    private static final String URL_MEM = "jdbc:h2:mem:testH2;MODE=MYSQL;DB_CLOSE_DELAY=-1";

    // 使用H2的TCP连接模式
    private static final String URL_TCP = "jdbc:h2:tcp://localhost/file:c:/H2DB/testH2tcp;MODE=MYSQL;DB_CLOSE_DELAY=-1";
    // 内存模式
    private static final String URL_TCP_MEM = "jdbc:h2:tcp://localhost/mem:testH2tcp;MODE=MYSQL;DB_CLOSE_DELAY=-1";

    private static final String NAME = "zheng";
    private static final String PASSWORD = "zheng";

    public static boolean insertToStudent(StudentDTO studentDTO) {
        try (Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD)) {
            String name = studentDTO.getName();
            String studentNo = studentDTO.getStudentNo();
            String department = studentDTO.getDepartment();
            String major = studentDTO.getMajor();
            String clazz = studentDTO.getClazz();


            Statement stmt = conn.createStatement();
            stmt.executeUpdate(
                    "insert into `student` (studentNo,name,department,major,clazz) values ('" + studentNo + "', '" + name + "','" + department + "','" + major + "','" + clazz + "');"
            );
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }


    public static void main(String[] args) {
        // 1.加载数据库驱动*
        // 2.获得数据库连接
        try (Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD)) {
            // 3.通过数据库的连接操作数据库，实现增删改查
            Statement stmt = conn.createStatement();

            //创建一个表
            stmt.executeUpdate(
                    "CREATE TABLE IF NOT EXISTS `student` (" +
                            "  `id` int(11) unsigned NOT NULL AUTO_INCREMENT," +
                            "  `studentNo` varchar(100) NOT NULL," +
                            "  `name` varchar(100) NOT NULL," +
                            "  `department` varchar(100) NOT NULL," +
                            "  `major` varchar(100) NOT NULL," +
                            "  `clazz` varchar(100) NOT NULL," +
                            "  PRIMARY KEY (`id`)" +
                            ");"
            );
            //插入一条数据
            stmt.executeUpdate(
                    "insert into `student` (studentNo,name,department,major,clazz) values ('201641412101', '郑斯钧','网络空间安全学院','软件工程','16软件2班');"
            );
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}
